package dao.mysql;

//import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import utility.LeaveUtil;

import dao.ConnPool;
import dao.DAOException;
import dao.LeaveDAO;
import dto.LeaveDTO;
import dto.LeaveSearchDTO;

public class LeaveDAOImpl implements LeaveDAO
{
	// private static final String dbUrl =
	// "jdbc:mysql://localhost:3306/ca_schemata";
	// private static final String dbUserName = "root";
	// private static final String dbPassword = "team1";
	SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
	// Connection conn = null;
	Statement stmt = null;
	ResultSet result = null;

	// public void connect()
	// {
	//
	// try
	// {
	// Class.forName("com.mysql.jdbc.Driver");
	//
	// }
	// catch (Exception e)
	// {
	// System.out.println("Failed to load JDBC driver.");
	// }
	// try
	// {
	// conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
	//
	// }
	// catch (Exception e)
	// {
	// System.out.println("Error occured at connect");
	// }
	// }
	//
	// public void closeconnect()
	// {
	// try
	// {
	// conn.close();
	// stmt.close();
	// if (result != null)
	// {
	// result.close();
	// }
	// }
	// catch (Exception e)
	// {
	// System.out.println("Error occured at closing");
	// System.out.println(e.toString());
	// }
	// }

	public LeaveDTO findLeave(int leaveID) throws DAOException
	{
		LeaveDTO chosen = new LeaveDTO();
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String.format(
					"Select * from ca_schemata.leaveinfo WHERE LeaveID = %d",
					leaveID);

			result = stmt.executeQuery(sql);

			chosen = LeaveUtil.loadLeaveDTO(result).get(0);
		}
		catch (Exception e)
		{
			System.out.println("find Leave by ID error");
			System.out.println(e.toString());
			chosen = null;
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}
		return chosen;
	}

	public ArrayList<LeaveDTO> getLeaveHistory(int userID) throws DAOException
	{
		ArrayList<LeaveDTO> leaveList = new ArrayList<LeaveDTO>();
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String.format(
					"Select * from ca_schemata.leaveinfo WHERE UserID = %d",
					userID);

			result = stmt.executeQuery(sql);

			leaveList = LeaveUtil.loadLeaveDTO(result);
		}
		catch (Exception e)
		{
			System.out.println("find all error");
			System.out.println(e.toString());
			leaveList = null;
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}

		return leaveList;
	}

	public void applyLeave(LeaveDTO leave) throws DAOException
	{

		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("INSERT INTO ca_schemata.leaveinfo (UserID,UserName,StartDate,StartDateHalfDay,EndDate,EndDateHalfDay,LeaveType,WorkDisseminationID,Reason,Status,ApprovingManager,ApplicationDate,RejectReason,Duration) VALUES (%d,'%s','%s','%s','%s','%s','%s','%s','%s','%s',%d,'%s','%s',%d)",
							leave.get_userID(), leave.get_userName(),
							formatter.format(leave.get_startDate()),
							leave.get_startDateHalfDay(),
							formatter.format(leave.get_endDate()),
							leave.get_endDateHalfDay(), leave.get_leaveType(),
							leave.get_workDissemination(), leave.get_reason(),
							leave.get_status(), leave.get_ApprovingManID(),
							formatter.format(leave.get_applicationDate()),
							leave.get_rejectReason(), leave.get_duration());

			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("insert leave error");
			System.out.println(e.toString());
		}
		finally
		{
			ConnPool.closeconnect(stmt, result);
		}

	}

	public void updateLeave(LeaveDTO leave) throws DAOException
	{
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("Update ca_schemata.leaveinfo SET UserID = %d ,UserName = '%s', StartDate = '%s', StartDateHalfDay = '%s', EndDate = '%s', EndDateHalfDay = '%s', LeaveType = '%s', WorkDisseminationID = '%s', Reason = '%s', Status = '%s', ApprovingManager = %d, ApplicationDate = '%s', RejectReason ='%s', Duration = %d WHERE LeaveID = %d ",
							leave.get_userID(), leave.get_userName(),
							formatter.format(leave.get_startDate()),
							leave.get_startDateHalfDay(),
							formatter.format(leave.get_endDate()),
							leave.get_endDateHalfDay(), leave.get_leaveType(),
							leave.get_workDissemination(), leave.get_reason(),
							leave.get_status(), leave.get_ApprovingManID(),
							formatter.format(leave.get_applicationDate()),
							leave.get_rejectReason(), leave.get_leaveID(),
							leave.get_duration());
			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("Error occured at update");
			System.out.println(e.toString());
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}
	}

	public void setLeaveStatus(String LeaveStatus, String RejectionReason,
			int ApplicantID) throws DAOException
	{
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("Update ca_schemata.leaveinfo SET  Status = '%s', RejectReason ='%s' WHERE LeaveID = %d ",
							LeaveStatus, RejectionReason, ApplicantID);
			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("Error occured at update");
			System.out.println(e.toString());
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}
	}

	public void deleteLeave(LeaveDTO leave) throws DAOException
	{
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("Update ca_schemata.leaveinfo SET Status = 'Deleted' WHERE LeaveID=%d",
							leave.get_leaveID());

			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("Error occured at delete");
			System.out.println(e.toString());
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}
	}

	public void removeFromDatabase(LeaveDTO leave) throws DAOException
	{
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String.format(
					"Delete From ca_schemata.leaveinfo WHERE leaveID='%d'",
					leave.get_leaveID());

			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("Error occured at removal");
			System.out.println(e.toString());
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}
	}

	public ArrayList<LeaveDTO> findLeaveByCriteria(LeaveSearchDTO criti)
			throws DAOException
	{
		ArrayList<LeaveDTO> leaveList = new ArrayList<LeaveDTO>();
		ArrayList<LeaveDTO> iniLeaveList = new ArrayList<LeaveDTO>();
		boolean nameExist = false;
		boolean idExist = false;
		boolean startRangeDate = false;
		boolean endRangeDate = false;
		if (criti.get_userName() != null)
			nameExist = true;
		if (criti.get_userID() != 0)
			idExist = true;
		if (criti.get_startDate() != null)
			startRangeDate = true;
		if (criti.get_endDate() != null)
			endRangeDate = true;

		try
		{

			String sql = null;

			stmt = ConnPool.GetConnection().createStatement();

			if (criti.get_ApprovingmanID() == 0)
			{
				sql = String.format("Select * from ca_schemata.leaveinfo");
			}

			else
			{
				sql = String
						.format("Select * from ca_schemata.leaveinfo WHERE ApprovingManager = %d",
								criti.get_ApprovingmanID());
			}

			result = stmt.executeQuery(sql);

			iniLeaveList = LeaveUtil.loadLeaveDTO(result);
			
			if (criti.is_searchByAppDate())//Search by Application date
			{

				if (idExist)
				{
					ArrayList<LeaveDTO> filteredResult = LeaveUtil
							.filterWithID(iniLeaveList, criti);

					leaveList = filteredResult;

					if (startRangeDate && endRangeDate)
					{
						leaveList = LeaveUtil.filterLeaveWithStartandEndRange(
								filteredResult, criti);
					}
					else if (startRangeDate && !endRangeDate)
					{
						leaveList = LeaveUtil.filterLeaveWithStartRange(
								filteredResult, criti);
					}
					else if (endRangeDate && !startRangeDate)
					{
						leaveList = LeaveUtil.filterLeaveWithEndRange(
								filteredResult, criti);
					}

				}

				else if (nameExist && !idExist)
				{
					ArrayList<LeaveDTO> filteredResult = LeaveUtil
							.filterWithName(iniLeaveList, criti);

					leaveList = filteredResult;

					if (startRangeDate && endRangeDate)
					{
						leaveList = LeaveUtil.filterLeaveWithStartandEndRange(
								filteredResult, criti);
					}

					else if (startRangeDate && !endRangeDate)
					{
						leaveList = LeaveUtil.filterLeaveWithStartRange(
								filteredResult, criti);
					}

					else if (endRangeDate && !startRangeDate)
					{
						leaveList = LeaveUtil.filterLeaveWithEndRange(
								filteredResult, criti);
					}

				}
				else if (startRangeDate || endRangeDate)
				{
					if (startRangeDate && endRangeDate)
					{
						leaveList = LeaveUtil.filterLeaveWithStartandEndRange(
								iniLeaveList, criti);
					}

					else if (startRangeDate && !endRangeDate)
					{
						leaveList = LeaveUtil.filterLeaveWithStartRange(
								iniLeaveList, criti);
					}
					else if (endRangeDate && !startRangeDate)
					{
						leaveList = LeaveUtil.filterLeaveWithEndRange(
								iniLeaveList, criti);
					}
				}

				else
				{
					leaveList = iniLeaveList;
				}
			}

			else if(!criti.is_searchByAppDate()) //Search by StartDate
			{
//				if (idExist)
//				{
//					ArrayList<LeaveDTO> filteredResult = LeaveUtil
//							.filterWithID(iniLeaveList, criti);

//					leaveList = filteredResult;

//					if (startRangeDate && endRangeDate)
//					{
						leaveList = LeaveUtil.filterLeaveWithStartandEndRangeByDate(
								iniLeaveList, criti);
//					}
//					else if (startRangeDate && !endRangeDate)
//					{
//						leaveList = LeaveUtil.filterLeaveWithStartRangeByDate(
//								filteredResult, criti);
//					}
//					else if (endRangeDate && !startRangeDate)
//					{
//						leaveList = LeaveUtil.filterLeaveWithEndRangeByDate(
//								filteredResult, criti);
//					}

//				}
//
//				else if (nameExist && !idExist)
//				{
//					ArrayList<LeaveDTO> filteredResult = LeaveUtil
//							.filterWithName(iniLeaveList, criti);
//
//					leaveList = filteredResult;
//
//					if (startRangeDate && endRangeDate)
//					{
//						leaveList = LeaveUtil.filterLeaveWithStartandEndRangeByDate(
//								filteredResult, criti);
//					}

//					else if (startRangeDate && !endRangeDate)
//					{
//						leaveList = LeaveUtil.filterLeaveWithStartRangeByDate(
//								filteredResult, criti);
//					}
//
//					else if (endRangeDate && !startRangeDate)
//					{
//						leaveList = LeaveUtil.filterLeaveWithEndRangeByDate(
//								filteredResult, criti);
//					}

//				}
//				else if (startRangeDate || endRangeDate)
//				{
//					if (startRangeDate && endRangeDate)
//					{
//						leaveList = LeaveUtil.filterLeaveWithStartandEndRangeByDate(
//								iniLeaveList, criti);
//					}

//					else if (startRangeDate && !endRangeDate)
//					{
//						leaveList = LeaveUtil.filterLeaveWithStartRangeByDate(
//								iniLeaveList, criti);
//					}
//					else if (endRangeDate && !startRangeDate)
//					{
//						leaveList = LeaveUtil.filterLeaveWithEndRangeByDate(
//								iniLeaveList, criti);
//					}
//				}

//				else
//				{
//					leaveList = iniLeaveList;
//				}
			}
		}

		catch (Exception e)
		{
			System.out.println("find by criteria By Date error");
			System.out.println(e.toString());
			leaveList = null;
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}

		return leaveList;

	}

	public ArrayList<LeaveDTO> findSubordinatesOnLeave(LeaveSearchDTO criti)
			throws DAOException
	{
		ArrayList<LeaveDTO> inileaveList = null;
		ArrayList<LeaveDTO> leaveList = null;
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("Select * from ca_schemata.leaveinfo WHERE ApprovingManager = %d",
							criti.get_userID());

			result = stmt.executeQuery(sql);

			inileaveList = LeaveUtil.loadLeaveDTO(result);

			leaveList = LeaveUtil
					.filterSurbodinatesOnLeave(inileaveList, criti);
		}
		catch (Exception e)
		{
			System.out.println("find subordinate leave error");
			System.out.println(e.toString());
			leaveList = null;
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}

		return leaveList;
	}

	public ArrayList<LeaveDTO> findSubordinatesOnLeave(LeaveSearchDTO criti,
			String leaveType) throws DAOException
	{
		ArrayList<LeaveDTO> inileaveList = null;
		ArrayList<LeaveDTO> leaveList = null;
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("Select * from ca_schemata.leaveinfo WHERE ApprovingManager = %d AND LeaveType = '%s'",
							criti.get_userID(), leaveType);

			result = stmt.executeQuery(sql);

			inileaveList = LeaveUtil.loadLeaveDTO(result);

			leaveList = LeaveUtil
					.filterSurbodinatesOnLeave(inileaveList, criti);
		}
		catch (Exception e)
		{
			System.out.println("find subordinate leave error");
			System.out.println(e.toString());
			leaveList = null;
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}

		return leaveList;
	}

	public ArrayList<LeaveDTO> findLeaveByAllSubordinates(int userID)
			throws DAOException
	{
		ArrayList<LeaveDTO> leaveList = new ArrayList<LeaveDTO>();
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("Select * from ca_schemata.leaveinfo WHERE ApprovingManager = %d",
							userID);

			result = stmt.executeQuery(sql);

			leaveList = LeaveUtil.loadLeaveDTO(result);
		}
		catch (Exception e)
		{

			System.out.println("find subordinate leave error");
			System.out.println(e.toString());
			leaveList = null;
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}

		return leaveList;
	}

	@Override
	public void updateLeaveSpent(int leaveSpent, int leaveID)
			throws DAOException
	{
		// TODO Auto-generated method stub
		try
		{
			stmt = ConnPool.GetConnection().createStatement();

			String sql = String
					.format("Update ca_schemata.leaveinfo SET LeaveSpent = %d WHERE LeaveID = %d ",
							leaveSpent, leaveID);

			stmt.execute(sql);
		}
		catch (Exception e)
		{
			System.out.println("Error occured at update leave spent");
			System.out.println(e.toString());
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}

	}
	
	public long getLastestManipulateRecordID() 
			throws DAOException	
	{
		long insertID = 0;
		try
		{
			stmt = ConnPool.GetConnection().createStatement();
			
			String sql = String
					.format("SELECT LAST_INSERT_ID() ");
			
			ResultSet  rs = stmt.executeQuery(sql);
			
			if (rs.next())  
			{  
				insertID = rs.getLong("last_insert_id()");              
			} 
		}
		catch (Exception e)
		{
			System.out.println("find latest ID error");
			System.out.println(e.toString());
		}

		finally
		{
			ConnPool.closeconnect(stmt, result);
		}
		return insertID;
	}
}
